﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[UpdateUpdateCheckDW]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- now
	declare @now datetime;
	set @now=current_timestamp;

	-- prepare
	select CheckDay,ReleaseId,sum(CheckCount) as CheckCount
	into #UpdateCheckDW
	from (
		select cast(convert(varchar(50),CheckDate,112) as int) as CheckDay,ReleaseId,count(*) as CheckCount
		from UpdateCheckLog
		where ReleaseId!=0
		and CheckDate<@now
		and InstanceId is null
		group by convert(varchar(50),CheckDate,112),ReleaseId

		union all

		select cast(convert(varchar(50),CheckDate,112) as int) as CheckDay,max(ReleaseId) as ReleaseId,1 as CheckCount
		from UpdateCheckLog
		where ReleaseId!=0
		and CheckDate<@now
		and InstanceId is not null
		group by convert(varchar(50),CheckDate,112),InstanceId
	) ul
	group by CheckDay,ReleaseId;

	-- update existing into data warehouse
	update UpdateCheckDW
	set UpdateCheckDW.CheckCount=(UpdateCheckDW.CheckCount+#UpdateCheckDW.CheckCount)
	from UpdateCheckDW
	inner join #UpdateCheckDW
	on UpdateCheckDW.CheckDay=#UpdateCheckDW.CheckDay
	and UpdateCheckDW.ReleaseId=#UpdateCheckDW.ReleaseId;

	-- insert new into data warehouse
	insert into UpdateCheckDW
	select CheckDay,ReleaseId,CheckCount
	from #UpdateCheckDW
	where not exists (
		select 1
		from UpdateCheckDW
		where #UpdateCheckDW.CheckDay=UpdateCheckDW.CheckDay
		and #UpdateCheckDW.ReleaseId=UpdateCheckDW.ReleaseId
	);

	-- cleanup check log
	delete from UpdateCheckLog
	where CheckDate<@now;
END
